//
//  KLSGDB.m
//  KaoLaShiGuangFM
//
//  Created by lanou3g on 15/11/5.
//  Copyright © 2015年 com.lanou3g. All rights reserved.
//

#import "KLSGDB.h"

@implementation KLSGDB

static sqlite3 *db = nil;

// 打开数据库
- (sqlite3 *)openDatabase
{
    if (db != nil) {
        return db;
    }
    
    // 1.设置文件名
    NSString *filename = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"KLSG.db"];
    NSLog(@"数据库：%@", filename);
    // 2.打开数据库文件，如果没有就会自动创建一个文件
    NSInteger result = sqlite3_open(filename.UTF8String, &db);
    if (result == SQLITE_OK) {
        NSLog(@"创建成功或者说是打开成功");
    } else {
        NSLog(@"创建失败或者说打开失败");
    }
    return  db;
}

// 关闭数据库
- (void)closeDB
{
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"关闭成功");
        db = nil;
    } else {
        NSLog(@"关闭失败");
    }
}

// 创建一个表
- (void)createTable
{
    // 1.打开数据库
    db = [self openDatabase];
    // 2.写创建表的SQL语句
    NSString *sql = @"create table IF NOT EXISTS kind(albumId text primary key not NULL, title text not NULL, intro text not NULL, albumCoverUrl290 text not NULL, playsCounts text not NULL, tracksCounts text not NULL)";
    // 执行SQL语句
    // char **errmsg错误信息
    char *error = nil;
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, &error);
    // 判断执行结果是否成功
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    } else {
        NSLog(@"创建表失败");
    }
    // 关闭数据库
    [self closeDB];
}

- (BOOL)insertWithKindModel:(KLSGKindModel *)model
{
    [self createTable];
    BOOL flag = NO;
   // 1.打开数据库
    db = [self openDatabase];
    // 判断插入的数据是否为空
    if (!model.albumId) {
        return flag;
    }
    // 写sql语句
    NSString *sql = [NSString stringWithFormat:@"insert into kind(albumId, title, intro, albumCoverUrl290, playsCounts, tracksCounts) values ('%@', '%@', '%@', '%@', '%@', '%@')", [NSString stringWithFormat:@"%@", model.albumId], model.title, model.intro, model.albumCoverUrl290,[NSString stringWithFormat:@"%@", model.playsCounts], [NSString stringWithFormat:@"%@", model.tracksCounts]];
    // 执行sql 语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    // 判断是否成功
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
        flag = YES;
    } else {
        NSLog(@"插入失败");
        flag = NO;
    }
    // 5.关闭数据库
    [self closeDB];
    return flag;
}

- (BOOL)insertWithEditorReModel:(KLSGEditorReModel *)model
{
    
    [self createTable];
    BOOL flag = NO;
    // 1.打开数据库
    db = [self openDatabase];
    // 判断插入的数据是否为空
    if (!model.albumId) {
        return flag;
    }
    // 写sql语句
    NSString *sql = [NSString stringWithFormat:@"insert into kind(albumId, title, intro, albumCoverUrl290,playsCounts, tracksCounts) values ('%@', '%@', '%@', '%@', '%@', '%@')", [NSString stringWithFormat:@"%@", model.albumId], model.title, model.intro, model.albumCoverUrl290,[NSString stringWithFormat:@"%@", model.playsCounts], [NSString stringWithFormat:@"%@", model.tracksCounts]];
    // 执行sql 语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    // 判断是否成功
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
        flag = YES;
    } else {
        NSLog(@"插入失败");
        flag = NO;
    }
    // 5.关闭数据库
    [self closeDB];
    return flag;
}

// 删除数据
- (BOOL)deleteKindWithAlbumId:(NSNumber *)albumId
{
    BOOL flag = NO;
    // 打开数据库
    [self openDatabase];
    NSString *sql = [NSString stringWithFormat:@"delete from kind where albumId = '%@'", [NSString stringWithFormat:@"%@", albumId]];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
        flag = YES;
    } else {
        NSLog(@"删除失败");
        flag = YES;
    }
    [self closeDB];
    return flag;
        
}

// 查询数据
- (BOOL)queryKindWithAlbumId:(NSNumber *)albumId
{
    [self openDatabase];
    NSString *sql = @"select * from kind where albumId = ?";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建stmt成功");
        
        sqlite3_bind_text(stmt, 1, [NSString stringWithFormat:@"%@", albumId].UTF8String, -1, NULL);
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            return YES;
        }
        return NO;
    }
    return NO;
}

- (NSArray *)queryAllKindModel
{
    [self openDatabase];
    NSString *sql = [NSString stringWithFormat:@"select * from kind"];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"执行成功");
        NSMutableArray *array = [NSMutableArray array];
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            char *albumId = (char *)sqlite3_column_text(stmt, 0);
            char *title = (char *)sqlite3_column_text(stmt, 1);
            char *intro = (char *)sqlite3_column_text(stmt, 2);
            char *playsCounts = (char *)sqlite3_column_text(stmt, 4);
            char *tracksCounts = (char *)sqlite3_column_text(stmt, 5);
            char *albumCoverUrl290 = (char *)sqlite3_column_text(stmt, 3);
            
            KLSGKindModel *model = [[KLSGKindModel alloc] init];
            
            model.albumId = [NSNumber numberWithInteger:[[NSString stringWithUTF8String:albumId] integerValue]];
            model.title = [NSString stringWithUTF8String:title];
            model.intro = [NSString stringWithUTF8String:intro];
            model.playsCounts = [NSNumber numberWithChar:*playsCounts];
            model.tracksCounts = [NSNumber numberWithChar:*tracksCounts];
            model.albumCoverUrl290 = [NSString stringWithUTF8String:albumCoverUrl290];
            [array addObject: model];
        }
        sqlite3_finalize(stmt);
        [self closeDB];
        
        return array;
    } else {
        NSLog(@"执行失败");
    }
    [self closeDB];
    return nil;
}


@end
